﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
using System.Text.RegularExpressions;

namespace AlphaSolutionsGP
{
    public partial class frmCourseView : Form
    {
        Course aCourse;
        bool toModify;
        //"create new" constructor
        public frmCourseView()
        {
            toModify = false;
            aCourse = new Course();
            InitializeComponent();
            comboCourseYear.SelectedIndex = 0;
        }
        //modify Constructor
        public frmCourseView(Course c)
        {
            toModify = true;
            aCourse = c;
            InitializeComponent();
            printCourseData();
            btnHelpCourse.Visible = false;
        }

        private void frmCourseView_Load(object sender, EventArgs e)
        {
            

        }

        //print data to the form
        void printCourseData()
        {
            txtCourseTitle.Text = aCourse.Title;
            txtCourseCode.Text = aCourse.CourseCode;
            comboCourseYear.Text = aCourse.Year.ToString();
            txtDepartment.Text = aCourse.Department;
            txtCourseMaxCapacity.Text = aCourse.MaxStudents.ToString();
            txtDescription.Text = aCourse.Description;
            txtRequirements.Text = aCourse.Description;

            //modules
            //for each year
            string pre = txtCourseCode.Text.Substring(0, 1);

            fillInCombo(comboY1M, "title", "MODULE",1,pre);
            fillInListBox(moduleListY1, "title", "MODULE", 1,pre);
            fillInCombo(comboY2M, "title", "MODULE", 2,pre);
            fillInListBox(moduleListY2, "title", "MODULE", 2,pre);
            fillInCombo(comboY3M, "title", "MODULE", 3,pre);
            fillInListBox(moduleListY3, "title", "MODULE", 3,pre);

            
        }

        //DINAMICALLY FILL IN A DROPDOWN MENU
        private void fillInCombo(ComboBox cb, string column, string table, int lvl, string codePrefix)
        {
            MySqlConnection connection = new MySqlConnection("server=194.81.104.22;" + "User Id=alpha;" + "database=alpha_db;" + "password=bravo;");
            try
            {
                connection.Open();
                MySqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = "SELECT DISTINCT " + column + " FROM " + table + " WHERE COURSE_course_Code IS NULL AND module_Code LIKE '" + codePrefix + lvl + "%'";
                MySqlDataReader myReader = cmd.ExecuteReader();
                string nameColumn = "" + column + "";
                while (myReader.Read())
                {
                    column = myReader.GetString(nameColumn);
                    cb.Items.Add(column);
                }
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show("Unable to fill table due to the following error:\n\n" + ex.ToString());
            }

            finally
            {
                connection.Close();

            }
        }
        //DINAMICALLY FILL IN A DROPDOWN MENU
        private void fillInListBox(ListBox lb, string column, string table, int lvl, string codePrefix)
        {
            MySqlConnection connection = new MySqlConnection("server=194.81.104.22;" + "User Id=alpha;" + "database=alpha_db;" + "password=bravo;");
            try
            {
                connection.Open();
                MySqlCommand cmd = connection.CreateCommand();
                cmd.CommandText = "SELECT " + column + " FROM " + table + " WHERE module_Code LIKE '"+ codePrefix + lvl + "%' AND COURSE_course_Code IS NOT NULL";
                MySqlDataReader myReader = cmd.ExecuteReader();
                string nameColumn = "" + column + "";
                while (myReader.Read())
                {
                    column = myReader.GetString(nameColumn);
                    lb.Items.Add(column);
                }
            }
            catch (Exception ex)
            {
                System.Windows.Forms.MessageBox.Show("Unable to fill table due to the following error:\n\n" + ex.ToString());
            }

            finally
            {
                connection.Close();

            }
        }

        private void btnCancel_Click(object sender, EventArgs e)
        {
            this.Close();
        }

        private void btnSave_Click(object sender, EventArgs e)
        {
            //validate form objects
            if (validate())
            {
                
                string cid = txtCourseCode.Text;
                cid.ToUpper();
                txtCourseCode.Text = cid;

                //data should be valid and clean at this point
                aCourse.Title = txtCourseTitle.Text;
                aCourse.CourseCode = txtCourseCode.Text;
                aCourse.Year = int.Parse(comboCourseYear.SelectedItem.ToString());
                aCourse.Department = txtDepartment.Text;
                aCourse.MaxStudents = int.Parse(txtCourseMaxCapacity.Text);
                aCourse.Description = txtDescription.Text;
                aCourse.Requirements = txtRequirements.Text;
                
                //if to modify, run modify sql else run create sql
                try
                {
                    if (toModify)
                        //create the new course with these values
                        aCourse.ammendCourse();
                    else
                        aCourse.createCourse();

                    this.Close();
                }
                catch (Exception ex)
                {
                    MessageBox.Show("An error occured, \n\n " + ex.ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                }
            }
        }

        /*
         * function to validate the data input into the form
         */
        private bool validate()
        {
            bool flag = true;
            string msg = "";
            if (txtCourseTitle.Text == "")
            {
                //MessageBox.Show("You must enter a Course Title");
                msg += "\n- Title must not be empty";
                flag = false;
            }
            Match matchCourseCode = Regex.Match(txtCourseCode.Text.Trim(), @"^[a-zA-Z][a-zA-Z][a-zA-Z][a-zA-Z][0-9][0-9]"); //check for integer numbers only within the string 
            if (!matchCourseCode.Success || txtCourseCode.Text == "" || txtCourseCode.Text.Length!=6) //if no characters were found
            {
                msg += "\n- Course code is blank or invalid. (LLLLNN)";
                flag = false;
            }
            if (comboCourseYear.SelectedItem == null)
            {
                MessageBox.Show("You must choose a Course Year");
                flag = false;
            }
            if (txtDepartment.Text == "")
            {
                //MessageBox.Show("You must enter a Course Department");
                msg += "\n- Department must not be empty";
                flag = false;
            }
            if (txtCourseMaxCapacity.Text == "")
            {
                //MessageBox.Show("You must enter a Maximum Capacity for the Course");
                msg += "\n- Maximum Capacity must not be empty";
                flag = false;
            }
            if (txtDescription.Text == "")
            {
                //MessageBox.Show("You must enter a Course Description");
                msg += "\n- Description must not be empty";
                flag = false;
            }
            if (txtRequirements.Text == "")
            {
                msg += "\n- Requirements must not be empty";
                flag = false;
            }

            if (!flag)
                MessageBox.Show(msg, "Validation Error(s)", MessageBoxButtons.OK, MessageBoxIcon.Error);

            return flag;
        }

        private void btnHelpCourse_Click(object sender, EventArgs e)
        {
            MessageBox.Show("You will need to fill the top part of this form and SAVE first before being able to choose the modules related to this course.", "Help", MessageBoxButtons.OK, MessageBoxIcon.Information);

        }

        private void comboY1M_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (moduleListY1.Items.Count < 6)
            {
                moduleListY1.Items.Add(comboY1M.SelectedItem);
                MySqlConnection connection = new MySqlConnection("server=194.81.104.22;" + "User Id=alpha;" + "database=alpha_db;" + "password=bravo;");
                try
                {
                    connection.Open();
                    MySqlCommand cmd = connection.CreateCommand();
                    cmd.CommandText = "SELECT module_Code FROM MODULE WHERE title='" + comboY1M.SelectedItem.ToString() + "'";
                    MySqlDataReader myReader = cmd.ExecuteReader();
                    myReader.Read();
                    string modCode = myReader.GetString("module_Code");
                    myReader.Close();
                    cmd.CommandText = "UPDATE MODULE SET COURSE_course_Code = '" + txtCourseCode.Text + "' WHERE module_Code = '" + modCode + "'";
                    myReader = cmd.ExecuteReader();
                    myReader.Close();

                }
                catch (Exception ex)
                {
                    System.Windows.Forms.MessageBox.Show("Unable to execute SQL due to the following error:\n\n" + ex.ToString());
                }

                finally
                {
                    connection.Close();

                }
                comboY1M.Items.Clear();
                fillInCombo(comboY1M, "title", "MODULE", 1, txtCourseCode.Text.Substring(0,1));
            }

        }

        private void comboY2M_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (moduleListY2.Items.Count < 6)
            {
                moduleListY2.Items.Add(comboY2M.SelectedItem);
                MySqlConnection connection = new MySqlConnection("server=194.81.104.22;" + "User Id=alpha;" + "database=alpha_db;" + "password=bravo;");
                try
                {
                    connection.Open();
                    MySqlCommand cmd = connection.CreateCommand();
                    cmd.CommandText = "SELECT module_Code FROM MODULE WHERE title='" + comboY2M.SelectedItem.ToString() + "'";
                    MySqlDataReader myReader = cmd.ExecuteReader();
                    myReader.Read();
                    string modCode = myReader.GetString("module_Code");
                    myReader.Close();
                    cmd.CommandText = "UPDATE MODULE SET COURSE_course_Code = '" + txtCourseCode.Text + "' WHERE module_Code = '" + modCode + "'";
                    myReader = cmd.ExecuteReader();
                    myReader.Close();

                }
                catch (Exception ex)
                {
                    System.Windows.Forms.MessageBox.Show("Unable to execute SQL due to the following error:\n\n" + ex.ToString());
                }

                finally
                {
                    connection.Close();

                }
                comboY2M.Items.Clear();
                fillInCombo(comboY2M, "title", "MODULE", 2,txtCourseCode.Text.Substring(0,1));
            }
        }

        private void comboY3M_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (moduleListY3.Items.Count < 5)
            {
                moduleListY3.Items.Add(comboY3M.SelectedItem);
                MySqlConnection connection = new MySqlConnection("server=194.81.104.22;" + "User Id=alpha;" + "database=alpha_db;" + "password=bravo;");
                try
                {
                    connection.Open();
                    MySqlCommand cmd = connection.CreateCommand();
                    cmd.CommandText = "SELECT module_Code FROM MODULE WHERE title='" + comboY3M.SelectedItem.ToString() + "'";
                    MySqlDataReader myReader = cmd.ExecuteReader();
                    myReader.Read();
                    string modCode = myReader.GetString("module_Code");
                    myReader.Close();
                    cmd.CommandText = "UPDATE MODULE SET COURSE_course_Code = '" + txtCourseCode.Text + "' WHERE module_Code = '" + modCode + "'";
                    myReader = cmd.ExecuteReader();
                    myReader.Close();

                }
                catch (Exception ex)
                {
                    System.Windows.Forms.MessageBox.Show("Unable to execute SQL due to the following error:\n\n" + ex.ToString());
                }

                finally
                {
                    connection.Close();

                }
                comboY3M.Items.Clear();
                fillInCombo(comboY3M, "title", "MODULE", 3, txtCourseCode.Text.Substring(0, 1));
            }
        }

        /*
         * function to convert text in the course code text box to upper case
         * after each letter is typed
         */
        private void txtCourseCode_Leave(object sender, EventArgs e)
        {
            String txt = txtCourseCode.Text;
            txtCourseCode.Text = "";
            txtCourseCode.Text = txt.ToUpper();
        }

    }
}
